USE ServerAudit;
GO

-- =========================================================================
-- Title: Create auditHistory table
-- Author: BORDERSTATES\nemsch
-- Create Date: 2016-06-06 14:12:51
-- Copyright: Border States Electric, Copyright (C) 2016
-- Description: Create and load auditHistory table from current data.
-- =========================================================================
SET XACT_ABORT ON;
SET NOCOUNT ON;

-- Make sure we're not in a transaction
IF (@@TRANCOUNT > 0)
BEGIN
	ROLLBACK;
END;

BEGIN TRANSACTION;

SAVE TRAN nws_ssms;

IF (
		EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'U'
				AND NAME = 'auditHistory'
			)
		)
BEGIN
	DROP TABLE auditHistory;
END;
GO

CREATE TABLE auditHistory (
	histId INT IDENTITY PRIMARY KEY
	, histDate DATETIME NOT NULL
	, createdOn DATETIME NOT NULL
	, createdBy VARCHAR(200) NOT NULL
	, modifiedOn DATETIME NOT NULL
	, modifiedBy VARCHAR(200) NOT NULL
	, notes TEXT NULL
	);

ALTER TABLE auditHistory ADD CONSTRAINT [ah_HD_def] DEFAULT(GETDATE())
FOR [histDate];

ALTER TABLE auditHistory ADD CONSTRAINT [ah_COn_def] DEFAULT(GETDATE())
FOR [createdOn];

ALTER TABLE auditHistory ADD CONSTRAINT [ah_CBy_def] DEFAULT(SUSER_NAME())
FOR [createdBy];

ALTER TABLE auditHistory ADD CONSTRAINT [ah_MOn_def] DEFAULT(GETDATE())
FOR [modifiedOn];

ALTER TABLE auditHistory ADD CONSTRAINT [ah_MBy_def] DEFAULT(SUSER_NAME())
FOR [modifiedBy];

ALTER TABLE auditHistory ADD CONSTRAINT [ah_Notes2_def] DEFAULT(NULL)
FOR [notes];

CREATE NONCLUSTERED INDEX [histDate_idx] ON [dbo].[auditHistory] ([histDate] ASC)
	WITH (
			STATISTICS_NORECOMPUTE = OFF
			, SORT_IN_TEMPDB = OFF
			, IGNORE_DUP_KEY = OFF
			, DROP_EXISTING = OFF
			, ONLINE = OFF
			, ALLOW_ROW_LOCKS = ON
			, ALLOW_PAGE_LOCKS = ON
			) ON [PRIMARY];

--INSERT INTO auditHistory (
--	histDate
--	, createdOn
--	, modifiedOn
--	)
--SELECT idatestart
--	, idatestart
--	, idatestart
--FROM (
--	SELECT MIN(idate) AS idatestart
--		, CAST(idate AS DATE) AS idate
--	FROM auditData
--	GROUP BY CAST(idate AS DATE)
--	) AS auditData
--ORDER BY idate;

SELECT *
FROM auditHistory
ORDER BY histDate;

-- Comment the next line to commit this query
--ROLLBACK TRANSACTION nws_ssms;
COMMIT;
GO


